const express = require("express");
const router = express.Router();
const path = require('path');
const fs = require('fs');
const pool = require("../pool.js");
const utils = require("../utils/index.js");
const moment = require("moment-timezone");
const multer = require("multer");
const axios = require("axios");
const CryptoJS = require("crypto-js");
const config = require("../config");
const { content } = require("googleapis/build/src/apis/content/index.js");
const apiKey = process.env.OPENAI_API_KEY;
// 配置 multer 的存储设置
const storage = multer.diskStorage({
    destination: function (req, file, cb) {
        // 设置文件上传目录
        cb(null, "/home/wwwroot/default/beautyImages");
    },
    filename: function (req, file, cb) {
        // 生成文件名
        cb(null, Date.now() + "-" + file.originalname);
    },
});

const upload = multer({ storage: storage });

// 创建文件上传的 POST 接口
router.post("/upload", upload.single("file"), (req, res) => {
    const file = req.file;
    const { pk } = req.body;
    if (!file) {
        return res.status(400).send("请上传一个文件。");
    }
    let fileUrl = ''
    //安卓qrcode 放ins2服务器

    fileUrl = `https://insdoss.freeapptools.com/beautyImages/${file.filename}`;



    // 返回文件存储地址
    res.send({
        code: 0,
        message: "success",
        fileUrl: fileUrl,
    });
});
// 加密函数
function encrypt(message, key, iv) {
    const keyHex = CryptoJS.enc.Utf8.parse(key);
    const ivHex = CryptoJS.enc.Utf8.parse(iv);
    // 加密模式为CBC，填充模式为PKCS7
    const encrypted = CryptoJS.DES.encrypt(message, keyHex, {
        iv: ivHex,
        mode: CryptoJS.mode.CBC,
        padding: CryptoJS.pad.Pkcs7,
    });
    return encrypted.toString();
}

// 解密函数
function decrypt(ciphertext, key, iv) {
    const keyHex = CryptoJS.enc.Utf8.parse(key);
    const ivHex = CryptoJS.enc.Utf8.parse(iv); // 使用key的前8个字符作为IV
    // 解密模式为CBC，填充模式为PKCS7
    const decrypted = CryptoJS.DES.decrypt(
        {
            ciphertext: CryptoJS.enc.Base64.parse(ciphertext),
        },
        keyHex,
        {
            iv: ivHex,
            mode: CryptoJS.mode.CBC,
            padding: CryptoJS.pad.Pkcs7,
        }
    );
    return decrypted.toString(CryptoJS.enc.Utf8);
}



// router.post("/face-analysis", async (req, res) => {
//     try {
//         const response = await axios.post(
//             'https://api.openai.com/v1/chat/completions',
//             {
//                 model: 'gpt-4o-2024-11-20',
//                 messages: [
//                     {
//                         role: 'system',
//                         content: [
//                             {
//                                 type: 'text',
//                                 text: `<目标>分析用户面部照片，提供妆容妆造建议</目标>
// <输入>用户面部照片</输入>
// <输出>
// 1.严格按以下JSON格式返回分析结果;
// 2.分析内容为英文;
// <JSON内容>
// 1."image_content"(string):详细描述图片中的人物，包括性别、人种、肤色、特征、发型、头发颜色等;
// 1."facial_features"(JSON)
// 1.1."face_shape"(JSON)
// 1.1.1."type"(string):从Oval、Round、Heart、Square、Oblong、Diamond的脸型中中匹配用户脸型，返回对应的名称，严格和给定名称相同。注意准确区分Heart、Square、Oblong、Diamond和其他脸型，你很倾向于将大部分脸型都匹配为Oval，这样是不准确的;
// 1.1.2."desc"(string):针对用户脸型的个性化说明文案。详细说明该脸型特征以及如何影响用户整体妆容妆造。文案风格生动活泼，使用emoji;
// 1.1.3."strengths"(string):用户脸型在妆造方面优势的个性化详细说明。文案风格生动活泼，使用emoji;
// 1.1.4."avoid"(string):用户脸型在妆造方面需要避免的详细说明。文案风格生动活泼，使用emoji;
// 1.2."eyes"(JSON):
// 1.2.1."type"(string):从Almond、Round、Protruding、Monolid、Downturned、Upturned、Close-set、Wide-set、Deep-set、Hooded中匹配用户眼睛形状，返回对应的名称，严格和给定名称相同。注意准确区分Almond和其他眼型，你很倾向于将大部分眼型都匹配为Almond，这样是不准确的;
// 1.2.2."desc","strengths","avoid"同"face_shape"要求;
// 1.3."nose"(JSON):
// 1.3.1."type"(string):从中Button Nose、Straight Nose、Hooked Nose、Flat Nose、Roman Nose、Upturned、Wide Nose、Narrow Nose、Hawk Nose、Snub Nose匹配用户鼻子形状，返回对应的名称，严格和给定名称相同。注意准确区分Straight Nose和其他鼻子形状，你很倾向于将大部分鼻子都匹配为Straight Nose，这样是不准确的;
// 1.3.2."desc","strengths","avoid"同"face_shape"要求;
// 1.4."lips"(JSON):
// 1.4.1."type"(string):从Thin Lips、Full Lips、Wide Lips、Round Lips、Heart-shaped、Uneven Lips、Top-heavy Lips、Bottom-heavy Lips中匹配用户嘴唇形状，返回对应的名称，严格和给定名称相同。注意准确区分Full Lips和其他嘴唇形状，你很倾向于将大部分嘴唇都匹配为Full Lips，这样是不准确的;
// 1.4.2."desc","strengths","avoid"同"face_shape"要求;
// 1.5."skin_tone"(JSON):
// 1.5.1."type"(string):从Fair、Light、Medium、Olive、Tan、Deep中匹配用户肤色，返回对应的名称，严格和给定名称相同;
// 1.5.2."desc","strengths","avoid"同"face_shape"要求;
// 2."makeup_style"(JSON)
// 2.1."style_name"(string):从Classic Elegance、Natural Beauty、Soft Glam、Bronzed Glow、Retro Chic、Asian Glow、Elegant Matte中匹配用户最合适的妆造风格，返回对应名称，严格和给定名称相同;
// 2.2."desc"(string):详细描述该妆造风格特点、用户具体匹配该风格的原因。文案风格生动活泼，使用emoji;
// 3."makeup_tips"(JSON List):结合用户脸部特点，给出高度定制化个性化的妆容妆造建议。每个建议一个JSON;
// 3.1."title"(string):个性化建议的简短标题，尽可能清晰简洁;
// 3.2."desc"(string):详细具体的建议内容。注意需要紧密结合用户特点做到高度定制化个性化，能够充分吸引用户的兴趣和情绪。文案风格生动活泼，使用emoji;
// </JSON内容>
// 3.返回结果前再次确认是否符合要求的JSON格式以及分析内容;
// 4.只返回JSON格式内容，不要添加除此以外的其他任何内容;
// </输出>`
//                             }
//                         ]
//                     },
//                     {
//                         role: 'user',
//                         content: [
//                             {
//                                 type: 'image_url',
//                                 image_url: {
//                                     url: 'https://insdoss.freeapptools.com/qrCode/1730713147243-financial-advisor.png'
//                                 }
//                             }
//                         ]
//                     }
//                 ],
//                 response_format: {
//                     type: 'text'
//                 },
//                 temperature: 0.7,
//                 max_tokens: 4095,
//                 top_p: 1,
//                 frequency_penalty: 0,
//                 presence_penalty: 0
//             },
//             {
//                 headers: {
//                     'Content-Type': 'application/json',
//                     'Authorization': `Bearer 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`
//                 }
//             }
//         );

//         console.log(response.data);
//         res.send({
//             code: 0,
//             msg: 'success',
//             data: response.data.choices[0].message
//         })
//     } catch (error) {
//         console.error('Error making API request:', error.response ? error.response.data : error.message);
//     }

// });

//获取扫脸版本信息列表
router.post('/getFaceVersionList', async (req, res) => {
    try {
        let sql = `SELECT version FROM face_analysis`;
        let result = await mysqlQuery(sql, []);
        const formattedResult = result.map(row => ({
            id: row.version,
            name: row.version
        }));

        res.send({
            data: formattedResult
        });
    } catch (error) {
        console.error("Error fetching versions:", error);
        res.status(500).json({ error: "Internal server error" });
    }
});

//新增扫脸版本配置信息
router.post('/addFaceVersion', async (req, res) => {
    const { version } = req.body;
    try {
        // 检查表中是否已经有相同的版本
        const existingVersion = await mysqlQuery('SELECT 1 FROM face_analysis WHERE version = ?', [version]);
        if (existingVersion.length > 0) {
            return res.send({ code: 1, msg: '版本已存在' });
        }
        // 获取表中最后一条记录
        const lastRecord = await mysqlQuery('SELECT * FROM face_analysis ORDER BY id DESC LIMIT 1', []);

        if (lastRecord.length === 0) {
            return res.send({ code: 1, msg: '没有可复制的记录' });
        }
        // 从最后一条记录中获取值，并修改版本
        let newRecord = { ...lastRecord[0], version };

        delete newRecord.id;
        newRecord.id = utils.createId();
        // 插入新记录
        const insertQuery = 'INSERT INTO face_analysis SET ?';
        await mysqlQuery(insertQuery, [newRecord]);

        res.send({ code: 0, msg: 'success' })

    } catch (error) {
        console.error(error);
        res.status(500).json({ error: '数据库操作失败，请稍后重试' });
    }
})
//查询条形码
router.post('/fetchBarcodeDetails', async (req, res) => {
    let parseData = req.body.query;
    let content = JSON.parse(decrypt(parseData, config.cryptoKey, config.cryptoIv));
    const chinaTime = new Date();
    console.log(content, 'content' + chinaTime.toLocaleString())
    if (content.code != 'OZqcJQLBUyevTh') return
    const { qrcode, version, pk } = content
    try {
        const response = await axios.get(
            `https://barcodes-data.p.rapidapi.com?query=${qrcode}`,
            {
                headers: {
                    'Content-Type': 'application/json',
                    'x-rapidapi-key': `5f9f84eca0msh10b3d656e566de8p154632jsn29032ea5d436`
                }
            }
        );
        console.log(response.data)
        let data = encrypt(
            JSON.stringify(response.data),
            config.cryptoKey,
            config.cryptoIv
        );
        res.send({
            code: 0,
            msg: 'success',
            data: data
        })
    } catch (error) {
        res.send({
            code: 1,
            msg: error.response ? error.response.data : error.message,
        })
    }




})
router.post("/face-analysis", async (req, res) => {

    let parseData = req.body.query;
    let content = JSON.parse(decrypt(parseData, config.cryptoKey, config.cryptoIv));
    const chinaTime = new Date();
    console.log(content, 'content' + chinaTime.toLocaleString())
    if (content.code != 'OZqcJQLBUyevTh') return
    const { imageUrl, version, pk } = content


    let sql = `SELECT * FROM face_analysis WHERE version = ?`;
    let params = [version || '1.0.4']; // Use the specified version or the default version
    let result = await mysqlQuery(sql, params);
    // If there is no result for the specified version, query all versions and get the latest one
    if (result.length === 0 && version) {
        sql = `
         SELECT * FROM face_analysis
        ORDER BY id DESC
        LIMIT 1
    `;
        result = await mysqlQuery(sql);
    }






    const { prompt_release, temperature, max_tokens, top_p, frequency_penalty, presence_penalty, model } = result[0]
    try {
        // 将 imageUrl 转换为 Base64
        // const imageResponse = await axios.get(imageUrl, { responseType: 'arraybuffer' });
        // const imageBase64 = Buffer.from(imageResponse.data, 'binary').toString('base64');
        // console.log(imageBase64)
        const response = await axios.post(
            'https://api.openai.com/v1/chat/completions',
            {
                model: model,
                messages: [
                    {
                        role: 'system',
                        content: [
                            {
                                type: 'text',
                                text: prompt_release
                            }
                        ]
                    },
                    {
                        role: 'user',
                        content: [
                            {
                                type: 'image_url',
                                image_url: {
                                    // url: 'https://www.the-sun.com/wp-content/uploads/sites/6/2020/05/stassi.jpeg'
                                    url: imageUrl
                                }
                            }
                        ]
                    }
                ],
                response_format: {
                    type: 'text'
                },
                temperature: Number(temperature),
                max_tokens: Number(max_tokens),
                top_p: Number(top_p),
                frequency_penalty: Number(frequency_penalty),
                presence_penalty: Number(presence_penalty)
            },
            {
                headers: {
                    'Content-Type': 'application/json',
                    'Authorization': `Bearer   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`
                }
            }
        );
        console.log(imageUrl, '结果' + chinaTime.toLocaleString())

        // 获取 token 消耗信息
        const { usage } = response.data;
        const { prompt_tokens, completion_tokens, total_tokens } = usage;
        // 存储 token 消耗到数据库
        const date = utils.formatDate1(new Date());
        let id = utils.createId();
        // 需要插入的内容
        let message = null;
        let url = imageUrl

        // 如果 completion_tokens 小于 200，则存储 message
        // if (completion_tokens < 200) {
        //     message = JSON.stringify(response.data.choices[0].message);

        // }
        if (response.data.choices[0].message) {
            message = JSON.stringify(response.data.choices[0].message);

        }
        const messageData = JSON.parse(message);
        const parsedData = extractAndParseJson(messageData.content);
        let code = parsedData.error_info.error_code
        const insertSql = `INSERT INTO ai_tokens (id,pk,code,version,date,type, prompt_tokens, completion_tokens, total_tokens,message,url,model,create_date,update_date) VALUES (?,?,?,?, ?,?,?, ?,?,?,?, ?, ?,?)`;
        await mysqlQuery(insertSql, [id, pk, code, version, date, 'face', prompt_tokens, completion_tokens, total_tokens, message, url, model, utils.formatDate(new Date()), utils.formatDate(new Date())]);
        let data = encrypt(
            JSON.stringify(response.data.choices[0].message),
            config.cryptoKey,
            config.cryptoIv
        );
        res.send({
            code: 0,
            msg: 'success',
            data: data
        })
    } catch (error) {
        console.error('Error making API request:', error.response ? error.response.data : error.message);
        res.send({
            code: 1,
            msg: error.response ? error.response.data : error.message,
        })
    }

});

//获取美妆版本信息列表
router.post('/getMakeupVersionList', async (req, res) => {
    try {
        let sql = `SELECT version FROM makeup_analysis`;
        let result = await mysqlQuery(sql, []);
        const formattedResult = result.map(row => ({
            id: row.version,
            name: row.version
        }));

        res.send({
            data: formattedResult
        });
    } catch (error) {
        console.error("Error fetching versions:", error);
        res.status(500).json({ error: "Internal server error" });
    }
});

//新增美妆版本配置信息
router.post('/addMakeupVersion', async (req, res) => {
    const { version } = req.body;
    try {
        // 检查表中是否已经有相同的版本
        const existingVersion = await mysqlQuery('SELECT 1 FROM makeup_analysis WHERE version = ?', [version]);
        if (existingVersion.length > 0) {
            return res.send({ code: 1, msg: '版本已存在' });
        }
        // 获取表中最后一条记录
        const lastRecord = await mysqlQuery('SELECT * FROM makeup_analysis ORDER BY id DESC LIMIT 1', []);

        if (lastRecord.length === 0) {
            return res.send({ code: 1, msg: '没有可复制的记录' });
        }
        // 从最后一条记录中获取值，并修改版本
        let newRecord = { ...lastRecord[0], version };

        // 假设我们知道 makeup_analysis 表中每个字段的名称，并且我们不复制主键 id
        delete newRecord.id;
        newRecord.id = utils.createId();
        // 插入新记录
        const insertQuery = 'INSERT INTO makeup_analysis SET ?';
        await mysqlQuery(insertQuery, [newRecord]);

        res.send({ code: 0, msg: 'success' })

    } catch (error) {
        console.error(error);
        res.status(500).json({ error: '数据库操作失败，请稍后重试' });
    }
})

router.post("/makeup-analysis", async (req, res) => {
    let parseData = req.body.query;

    let content = JSON.parse(decrypt(parseData, config.cryptoKey, config.cryptoIv));

    if (content.code != 'OZqcJQLBUyevTh') return

    const { imageUrl, version, pk } = content








    let sql = `SELECT * FROM makeup_analysis WHERE version = ?`;
    let params = [version || '1.0.4']; // Use the specified version or the default version
    let result = await mysqlQuery(sql, params);
    // If there is no result for the specified version, query all versions and get the latest one
    if (result.length === 0 && version) {
        sql = `
         SELECT * FROM makeup_analysis
        ORDER BY id DESC
        LIMIT 1
    `;
        result = await mysqlQuery(sql);
    }



    const { prompt_release, temperature, max_tokens, top_p, frequency_penalty, presence_penalty, model } = result[0]


    try {
        const response = await axios.post(
            'https://api.openai.com/v1/chat/completions',
            {
                model: model,
                messages: [
                    {
                        role: 'system',
                        content: [
                            {
                                type: 'text',
                                text: prompt_release
                            }
                        ]
                    },
                    {
                        role: 'user',
                        content: [
                            {
                                type: 'image_url',
                                image_url: {
                                    // url: 'https://www.the-sun.com/wp-content/uploads/sites/6/2020/05/stassi.jpeg'
                                    url: imageUrl
                                }
                            }
                        ]
                    }
                ],
                response_format: {
                    type: 'text'
                },
                temperature: Number(temperature),
                max_tokens: Number(max_tokens),
                top_p: Number(top_p),
                frequency_penalty: Number(frequency_penalty),
                presence_penalty: Number(presence_penalty)
            },
            {
                headers: {
                    'Content-Type': 'application/json',
                    'Authorization': `Bearer   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`
                }
            }
        );
        // 获取 token 消耗信息
        const { usage } = response.data;
        const { prompt_tokens, completion_tokens, total_tokens } = usage;
        // 存储 token 消耗到数据库
        const date = utils.formatDate1(new Date());
        let id = utils.createId();
        // 需要插入的内容
        let message = null;
        let url = imageUrl

        // 如果 completion_tokens 小于 200，则存储 message
        // if (completion_tokens < 200) {
        //     message = JSON.stringify(response.data.choices[0].message);
        // }
        if (response.data.choices[0].message) {
            message = JSON.stringify(response.data.choices[0].message);
        }
        const messageData = JSON.parse(message);
        const parsedData = extractAndParseJson(messageData.content);
        let code = parsedData.error_info.error_code
        const insertSql = `INSERT INTO ai_tokens (id,pk,code,version,date,type, prompt_tokens, completion_tokens, total_tokens,message,url,model,create_date,update_date) VALUES (?,?,?,?, ?,?,?,?, ?,?,?, ?,?, ?)`;
        await mysqlQuery(insertSql, [id, pk, code, version, date, 'makeup', prompt_tokens, completion_tokens, total_tokens, message, url, model, utils.formatDate(new Date()), utils.formatDate(new Date())]);
        let data = encrypt(
            JSON.stringify(response.data.choices[0].message),
            config.cryptoKey,
            config.cryptoIv
        );
        res.send({
            code: 0,
            msg: 'success',
            data: data
        })
    } catch (error) {
        console.error('Error making API request:', error.response ? error.response.data : error.message);
        res.send({
            code: 1,
            msg: error.response ? error.response.data : error.message,
        })
    }

});

//获取产品版本信息列表
router.post('/getProductVersionList', async (req, res) => {
    try {
        let sql = `SELECT version FROM product_recognition`;
        let result = await mysqlQuery(sql, []);
        const formattedResult = result.map(row => ({
            id: row.version,
            name: row.version
        }));

        res.send({
            data: formattedResult
        });
    } catch (error) {
        console.error("Error fetching versions:", error);
        res.status(500).json({ error: "Internal server error" });
    }
});

//新增美妆版本配置信息
router.post('/addProductVersion', async (req, res) => {
    const { version } = req.body;
    try {
        // 检查表中是否已经有相同的版本
        const existingVersion = await mysqlQuery('SELECT 1 FROM product_recognition WHERE version = ?', [version]);
        if (existingVersion.length > 0) {
            return res.send({ code: 1, msg: '版本已存在' });
        }
        // 获取表中最后一条记录
        const lastRecord = await mysqlQuery('SELECT * FROM product_recognition ORDER BY id DESC LIMIT 1', []);

        if (lastRecord.length === 0) {
            return res.send({ code: 1, msg: '没有可复制的记录' });
        }
        // 从最后一条记录中获取值，并修改版本
        let newRecord = { ...lastRecord[0], version };

        // 假设我们知道 product_recognition 表中每个字段的名称，并且我们不复制主键 id
        delete newRecord.id;
        newRecord.id = utils.createId();
        // 插入新记录
        const insertQuery = 'INSERT INTO product_recognition SET ?';
        await mysqlQuery(insertQuery, [newRecord]);

        res.send({ code: 0, msg: 'success' })

    } catch (error) {
        console.error(error);
        res.status(500).json({ error: '数据库操作失败，请稍后重试' });
    }
})
function extractAndParseJson(content) {
    try {
        // 阶段1：提取核心JSON字符串
        let jsonStr = content;

        // 处理带代码块的情况
        if (content.includes('```json')) {
            jsonStr = content.split('```json')[1].split('```')[0].trim();
        }
        // 处理带其他代码块的情况
        else if (content.startsWith('```')) {
            jsonStr = content.replace(/^```/gm, '').trim();
        }

        // 阶段2：清洗特殊字符
        const sanitized = jsonStr
            .replace(/\\n/g, '')     // 移除转义换行
            .replace(/\\"/g, '"')   // 转换转义引号
            .replace(/[\u0000-\u001F]/g, ''); // 移除控制字符

        // 阶段3：尝试解析
        return JSON.parse(sanitized);
    } catch (error) {
        console.error('JSON解析失败:', error.message);
        console.debug('原始内容:', content);
        return null;
    }
}

router.post("/product-recognition", async (req, res) => {
    let parseData = req.body.query;
    let content = JSON.parse(decrypt(parseData, config.cryptoKey, config.cryptoIv));
    if (content.code != 'OZqcJQLBUyevTh') return
    const { imageUrl, name, brand, version, pk } = content






    let sql = `SELECT * FROM product_recognition WHERE version = ?`;
    let params = [version || '1.0.4']; // Use the specified version or the default version
    let result = await mysqlQuery(sql, params);
    // If there is no result for the specified version, query all versions and get the latest one
    if (result.length === 0 && version) {
        sql = `
         SELECT * FROM product_recognition
        ORDER BY id DESC
        LIMIT 1
    `;
        result = await mysqlQuery(sql);
    }







    const { prompt_release, temperature, max_tokens, top_p, frequency_penalty, presence_penalty, model } = result[0]
    let queryContent;
    if (imageUrl) {
        queryContent = [{
            type: 'image_url',
            image_url: {
                url: imageUrl
            }
        }];
    } else {
        let obj = { name: name, brand: brand }
        queryContent = [
            {
                type: 'text',
                text: JSON.stringify(obj)
            }

        ];
    }
    try {
        const response = await axios.post(
            'https://api.openai.com/v1/chat/completions',
            {
                model: model,
                messages: [
                    {
                        role: 'system',
                        content: [
                            {
                                type: 'text',
                                text: prompt_release
                            }
                        ]
                    },
                    {
                        role: 'user',
                        content: queryContent
                    }
                ],
                response_format: {
                    type: 'text'
                },
                temperature: Number(temperature),
                max_tokens: Number(max_tokens),
                top_p: Number(top_p),
                frequency_penalty: Number(frequency_penalty),
                presence_penalty: Number(presence_penalty)
            },
            {
                headers: {
                    'Content-Type': 'application/json',
                    'Authorization': `Bearer   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`
                }
            }
        );
        // 获取 token 消耗信息
        const { usage } = response.data;
        const { prompt_tokens, completion_tokens, total_tokens } = usage;
        // 存储 token 消耗到数据库
        const date = utils.formatDate1(new Date());
        let id = utils.createId();
        // 需要插入的内容
        let message = null;
        let url = imageUrl

        // 如果 completion_tokens 小于 200，则存储 message
        // if (completion_tokens < 200) {
        //     message = JSON.stringify(response.data.choices[0].message);
        // }
        if (response.data.choices[0].message) {
            message = JSON.stringify(response.data.choices[0].message);
        }
        let product;
        if (imageUrl) {
            product = null
        } else {
            product = `name:${name},brand:${brand}`
        }
        const messageData = JSON.parse(message);
        const parsedData = extractAndParseJson(messageData.content);
        let code = parsedData.warning_info.warning_code
        const insertSql = `INSERT INTO ai_tokens (id,pk,code,version,date,type, prompt_tokens, completion_tokens, total_tokens,message,url,model,create_date,update_date,product) VALUES (?,?,?,?, ?,?, ?,?,?, ?,?,?, ?,?, ?)`;
        await mysqlQuery(insertSql, [id, pk, code, version, date, 'product', prompt_tokens, completion_tokens, total_tokens, message, url, model, utils.formatDate(new Date()), utils.formatDate(new Date()), product]);
        let data = encrypt(
            JSON.stringify(response.data.choices[0].message),
            config.cryptoKey,
            config.cryptoIv
        );
        res.send({
            code: 0,
            msg: 'success',
            data: data
        })
    } catch (error) {
        console.error('Error making API request:', error.response ? error.response.data : error.message);
        res.send({
            code: 1,
            msg: error.response ? error.response.data : error.message,
        })
    }

});
router.post("/getFaceConfig", async (req, res) => {
    const { version } = req.body
    let sql = `SELECT * FROM face_analysis WHERE version = ?`;
    let result = await mysqlQuery(sql, [version]);
    res.send({ code: 0, data: result[0] })
});
router.post("/getMakeupConfig", async (req, res) => {
    const { version } = req.body
    let sql = `SELECT * FROM makeup_analysis WHERE version = ?`;
    let result = await mysqlQuery(sql, [version]);
    res.send({ code: 0, data: result[0] })
});

router.post("/getProductConfig", async (req, res) => {
    const { version } = req.body
    let sql = `SELECT * FROM product_recognition WHERE version = ?`;
    let result = await mysqlQuery(sql, [version]);



    res.send({ code: 0, data: result[0] })
});
//更新face-analysis配置
router.post("/updateFaceConfig", async (req, res) => {
    let { name } = await utils.getUserInfo(req, res);
    const { prompt_test, temperature_test, max_tokens_test, top_p_test, frequency_penalty_test, presence_penalty_test, model_test, id } = req.body
    const sql = `
            UPDATE face_analysis 
            SET 
                prompt_test = ?, 
                temperature_test = ?, 
                max_tokens_test = ?, 
                top_p_test = ?, 
                frequency_penalty_test = ?, 
                presence_penalty_test = ?, 
                model_test = ?, 
                update_by = ?,
                update_date = ?
            WHERE id = ?
        `;
    let result = await mysqlQuery(sql, [prompt_test, temperature_test, max_tokens_test, top_p_test, frequency_penalty_test, presence_penalty_test, model_test, name, utils.formatDate(new Date()), id])
    res.send({ code: 0, data: result[0] })
});
router.post("/updateFaceConfigHandel", async (req, res) => {
    let { name } = await utils.getUserInfo(req, res);
    const { prompt_test, temperature_test, max_tokens_test, top_p_test, frequency_penalty_test, presence_penalty_test, model_test, id } = req.body
    const sql = `
            UPDATE face_analysis 
            SET 
                prompt_release = ?,
                temperature = ?,
                max_tokens = ?,
                top_p = ?,
                frequency_penalty = ?,
                presence_penalty = ?,
                model = ?,
                update_by = ?,
                update_date = ?
            WHERE id = ?
        `;
    let result = await mysqlQuery(sql, [prompt_test, temperature_test, max_tokens_test, top_p_test, frequency_penalty_test, presence_penalty_test, model_test, name, utils.formatDate(new Date()), id])
    res.send({ code: 0, data: result[0] })
});
router.post("/updateMakeupConfig", async (req, res) => {
    let { name } = await utils.getUserInfo(req, res);
    const { prompt_test, temperature_test, max_tokens_test, top_p_test, frequency_penalty_test, presence_penalty_test, model_test, id } = req.body
    const sql = `
            UPDATE makeup_analysis
            SET 
                prompt_test = ?, 
                temperature_test = ?, 
                max_tokens_test = ?, 
                top_p_test = ?, 
                frequency_penalty_test = ?, 
                presence_penalty_test = ?, 
                model_test = ?, 
                update_by = ?,
                update_date = ?
            WHERE id = ?
        `;
    let result = await mysqlQuery(sql, [prompt_test, temperature_test, max_tokens_test, top_p_test, frequency_penalty_test, presence_penalty_test, model_test, name, utils.formatDate(new Date()), id])
    res.send({ code: 0, data: result[0] })
});
router.post("/updateMakeupConfigHandel", async (req, res) => {
    let { name } = await utils.getUserInfo(req, res);
    const { prompt_test, temperature_test, max_tokens_test, top_p_test, frequency_penalty_test, presence_penalty_test, model_test, id } = req.body
    const sql = `
            UPDATE makeup_analysis
            SET 
                prompt_release = ?,
                temperature = ?,
                max_tokens = ?,
                top_p = ?,
                frequency_penalty = ?,
                presence_penalty = ?,
                model = ?,
                update_by = ?,
                update_date = ?
            WHERE id = ?
        `;
    let result = await mysqlQuery(sql, [prompt_test, temperature_test, max_tokens_test, top_p_test, frequency_penalty_test, presence_penalty_test, model_test, name, utils.formatDate(new Date()), id])
    res.send({ code: 0, data: result[0] })
});
router.post("/updateProductConfig", async (req, res) => {
    let { name } = await utils.getUserInfo(req, res);
    const { prompt_test, temperature_test, max_tokens_test, top_p_test, frequency_penalty_test, presence_penalty_test, model_test, id } = req.body
    const sql = `
            UPDATE product_recognition
            SET 
                prompt_test = ?, 
                temperature_test = ?, 
                max_tokens_test = ?, 
                top_p_test = ?, 
                frequency_penalty_test = ?, 
                presence_penalty_test = ?, 
                model_test = ?, 
                update_by = ?,
                update_date = ?
            WHERE id = ?
        `;
    let result = await mysqlQuery(sql, [prompt_test, temperature_test, max_tokens_test, top_p_test, frequency_penalty_test, presence_penalty_test, model_test, name, utils.formatDate(new Date()), id])
    res.send({ code: 0, data: result[0] })
});
router.post("/updateProductConfigHandel", async (req, res) => {
    let { name } = await utils.getUserInfo(req, res);
    const { prompt_test, temperature_test, max_tokens_test, top_p_test, frequency_penalty_test, presence_penalty_test, model_test, id } = req.body
    const sql = `
            UPDATE product_recognition
            SET 
                prompt_release = ?,
                temperature = ?,
                max_tokens = ?,
                top_p = ?,
                frequency_penalty = ?,
                presence_penalty = ?,
                model = ?,
                update_by = ?,
                update_date = ?
            WHERE id = ?
        `;
    let result = await mysqlQuery(sql, [prompt_test, temperature_test, max_tokens_test, top_p_test, frequency_penalty_test, presence_penalty_test, model_test, name, utils.formatDate(new Date()), id])
    console.log(result)
    res.send({ code: 0, data: result[0] })
});
router.post("/getImageError", async (req, res) => {
    let { page, size } = utils.pageSize(req.body.page, req.body.size);
    let { type, dateRange, pk, code } = req.body;
    let sqlConditions = [];
    const sqlParams = [];
    if (type) {
        sqlConditions.push("type = ?");
        sqlParams.push(type);
    }
    if (code) {
        sqlConditions.push("code = ?");
        sqlParams.push(code);
    }
    if (dateRange && dateRange.start && dateRange.end) {
        const startDate = moment(dateRange.start).startOf('day').format('YYYY-MM-DD HH:mm:ss');
        const endDate = moment(dateRange.end).endOf('day').format('YYYY-MM-DD HH:mm:ss');

        sqlConditions.push("update_date BETWEEN ? AND ?");
        sqlParams.push(startDate, endDate);
    }
    // 根据 pk 参数添加条件
    if (pk) {
        if (pk == "com.tcdemotc.testApp") {
            sqlConditions.push("pk = ?");
            sqlParams.push(pk);
        } else if (pk == "com.seamobitech.BeautyAI") {
            sqlConditions.push("(pk = ? OR pk IS NULL OR pk = '')");
            sqlParams.push(pk);
        }
    }
    // 添加条件：确保 message 字段有值
    // sqlConditions.push("message IS NOT NULL AND message != ''");
    // sqlConditions.push("completion_tokens < 200");
    sqlConditions.push("state = 0");

    sqlConditions.push("completion_tokens < 200 AND message IS NOT NULL AND message != ''");


    let sqlBase = `SELECT * FROM ai_tokens`;
    let totalBase = "FROM ai_tokens";

    if (sqlConditions.length > 0) {
        sqlBase += ` WHERE ${sqlConditions.join(" AND ")}`;
        totalBase += ` WHERE ${sqlConditions.join(" AND ")}`;
    }

    let sqlCount = `SELECT COUNT(*) AS total_count ${totalBase}`;

    let { total } = await utils.getListSum(sqlCount, sqlParams, res, req);

    sqlBase += " ORDER BY update_date DESC LIMIT ? OFFSET ?";
    sqlParams.push(size, page);
    await pool.query(sqlBase, sqlParams, (err, result) => {
        if (err) return res.send(utils.returnData({ code: -1, err, req }));
        result.forEach((element) => {

            element.update_date = moment(element.update_date)
                .tz("Asia/Shanghai")
                .format("YYYY-MM-DD HH:mm:ss");

        });
        res.send(
            utils.returnData({
                data: result,
                total: total,
            })
        );
    });
});
router.post("/getImageList", async (req, res) => {
    let { page, size } = utils.pageSize(req.body.page, req.body.size);
    let { type, dateRange, pk, code } = req.body;
    let sqlConditions = [];
    const sqlParams = [];
    if (type) {
        sqlConditions.push("type = ?");
        sqlParams.push(type);
    }
    if (code) {
        sqlConditions.push("code = ?");
        sqlParams.push(code);
    }
    if (dateRange && dateRange.start && dateRange.end) {
        const startDate = moment(dateRange.start).startOf('day').format('YYYY-MM-DD HH:mm:ss');
        const endDate = moment(dateRange.end).endOf('day').format('YYYY-MM-DD HH:mm:ss');

        sqlConditions.push("update_date BETWEEN ? AND ?");
        sqlParams.push(startDate, endDate);
    }
    // 根据 pk 参数添加条件
    if (pk) {
        if (pk == "com.tcdemotc.testApp") {
            sqlConditions.push("pk = ?");
            sqlParams.push(pk);
        } else if (pk == "com.seamobitech.BeautyAI") {
            sqlConditions.push("(pk = ? OR pk IS NULL OR pk = '')");
            sqlParams.push(pk);
        }
    }
    sqlConditions.push("state = 0");
    // 添加条件：确保 message 字段有值
    sqlConditions.push("completion_tokens > 200");

    let sqlBase = `SELECT * FROM ai_tokens `;
    let totalBase = "FROM ai_tokens";

    if (sqlConditions.length > 0) {
        sqlBase += ` WHERE ${sqlConditions.join(" AND ")}`;
        totalBase += ` WHERE ${sqlConditions.join(" AND ")}`;
    }

    let sqlCount = `SELECT COUNT(*) AS total_count ${totalBase}`;

    let { total } = await utils.getListSum(sqlCount, sqlParams, res, req);

    sqlBase += " ORDER BY update_date DESC LIMIT ? OFFSET ?";
    sqlParams.push(size, page);
    await pool.query(sqlBase, sqlParams, (err, result) => {
        if (err) return res.send(utils.returnData({ code: -1, err, req }));
        result.forEach((element) => {

            element.update_date = moment(element.update_date)
                .tz("Asia/Shanghai")
                .format("YYYY-MM-DD HH:mm:ss");

        });
        res.send(
            utils.returnData({
                data: result,
                total: total,
            })
        );
    });
});
router.post("/deleteHandel", async (req, res) => {
    let { id, url } = req.body;
    if (url) {
        // 使用示例
        const directoryPath = '/home/wwwroot/default/beautyImages';
        const fileName = getFileName(url);

        deleteFile(directoryPath, fileName);
    }

    const sql = `UPDATE ai_tokens SET state = 1 WHERE id = ?`;
    let result = await mysqlQuery(sql, [id])

    res.send(
        {
            code: 0,
            msg: 'success'
        }
    );
});
function getFileName(url) {
    const prefix = "https://insdoss.freeapptools.com/beautyImages/";
    if (url.startsWith(prefix)) {
        return url.slice(prefix.length);
    } else {
        return url;  // 如果URL不包含前缀，返回原始URL
    }
}

router.post("/batchDeleteHandel", async (req, res) => {
    const items = req.body.items;

    const directoryPath = '/home/wwwroot/default/beautyImages';

    for (const item of items) {
        const { id, url } = item;
        if (url) {
            const fileName = getFileName(url);

            await deleteFile(directoryPath, fileName);
        }


        const sql = `UPDATE ai_tokens SET state = 1 WHERE id = ?`;
        await mysqlQuery(sql, [id]);
    }

    res.send({
        code: 0,
        msg: 'success'
    });
});
// 时间转换函数，只保留 "月-日" 部分
function convertToChinaTime(utcDateStr) {
    const utcDate = new Date(utcDateStr);
    const chinaDate = new Date(utcDate.getTime() + 8 * 60 * 60 * 1000); // 添加8小时
    const year = chinaDate.getUTCFullYear(); // 提取年份，后续比较
    const month = String(chinaDate.getUTCMonth() + 1).padStart(2, '0'); // 月份从0开始，需要加1
    const day = String(chinaDate.getUTCDate()).padStart(2, '0'); // 日期
    return `${month}-${day}`;
}
//face token 图表数据
router.post('/getFaceTokens', async (req, res) => {
    const { dateRange } = req.body;
    let sql = `SELECT * FROM token_usage_per_day`;
    let params = [];

    if (dateRange && dateRange.start && dateRange.end) {
        const startDate = moment(dateRange.start).startOf('day').format('YYYY-MM-DD HH:mm:ss');
        const endDate = moment(dateRange.end).endOf('day').format('YYYY-MM-DD HH:mm:ss');

        sql += ` WHERE date BETWEEN ? AND ?`;
        params.push(startDate, endDate);
    }
    try {
        const results = await mysqlQuery(sql, params);

        // 遍历 results，转换 date 和 update_date 字段
        const convertedResults = results.map(result => {
            const convertedResult = { ...result };

            if (convertedResult.date) {
                convertedResult.date = convertToChinaTime(convertedResult.date);
            }

            if (convertedResult.update_date) {
                convertedResult.update_date = convertToChinaTime(convertedResult.update_date);
            }

            return convertedResult;
        });

        res.send(
            utils.returnData({
                data: convertedResults
            })
        );
    } catch (error) {
        res.status(500).json({
            success: false,
            message: 'Internal Server Error'
        });
    }
})
// router.post('/getFaceTokens', async (req, res) => {
//     const { type, dateRange } = req.body;
//     let sql = `SELECT * FROM token_usage_per_day where type = ?`;
//     let params = [type];

//     if (dateRange && dateRange.start && dateRange.end) {
//         const startDate = moment(dateRange.start).startOf('day').format('YYYY-MM-DD HH:mm:ss');
//         const endDate = moment(dateRange.end).endOf('day').format('YYYY-MM-DD HH:mm:ss');

//         sql += ` AND date BETWEEN ? AND ?`;
//         params.push(startDate, endDate);
//     }
//     try {
//         const results = await mysqlQuery(sql, params);

//         // 遍历 results，转换 date 和 update_date 字段
//         const convertedResults = results.map(result => {
//             const convertedResult = { ...result };

//             if (convertedResult.date) {
//                 convertedResult.date = convertToChinaTime(convertedResult.date);
//             }

//             if (convertedResult.update_date) {
//                 convertedResult.update_date = convertToChinaTime(convertedResult.update_date);
//             }

//             return convertedResult;
//         });

//         res.send(
//             utils.returnData({
//                 data: convertedResults
//             })
//         );
//     } catch (error) {
//         res.status(500).json({
//             success: false,
//             message: 'Internal Server Error'
//         });
//     }
// })
//总 token 图表数据
router.post('/getTokens', async (req, res) => {
    const { dateRange } = req.body;
    let sql = `SELECT * FROM token_usage_per_day WHERE 1=1`;
    let params = [];

    if (dateRange && dateRange.start && dateRange.end) {
        const startDate = moment(dateRange.start).startOf('day').format('YYYY-MM-DD HH:mm:ss');
        const endDate = moment(dateRange.end).endOf('day').format('YYYY-MM-DD HH:mm:ss');

        sql += ` AND date BETWEEN ? AND ?`;
        params.push(startDate, endDate);
    }

    try {
        const results = await mysqlQuery(sql, params);

        const aggregatedData = {};

        results.forEach(row => {
            const {
                date, prompt_tokens, completion_tokens, total_tokens,
                prompt_dollar, completion_dollar, total_dollar, model, update_date
            } = row;

            if (!aggregatedData[date]) {
                aggregatedData[date] = {
                    date,
                    prompt_tokens: 0,
                    completion_tokens: 0,
                    total_tokens: 0,
                    prompt_dollar: 0,
                    completion_dollar: 0,
                    total_dollar: 0,
                    update_date: update_date,
                    model: model,
                };
            }

            aggregatedData[date].prompt_tokens += prompt_tokens;
            aggregatedData[date].completion_tokens += completion_tokens;
            aggregatedData[date].total_tokens += total_tokens;

            // 使用四舍五入并转换为数字
            aggregatedData[date].prompt_dollar = parseFloat((aggregatedData[date].prompt_dollar + prompt_dollar).toFixed(4));
            aggregatedData[date].completion_dollar = parseFloat((aggregatedData[date].completion_dollar + completion_dollar).toFixed(4));
            aggregatedData[date].total_dollar = parseFloat((aggregatedData[date].total_dollar + total_dollar).toFixed(4));
        });

        const aggregatedArray = Object.values(aggregatedData);
        const convertedResults = aggregatedArray.map(result => {
            const convertedResult = { ...result };

            if (convertedResult.date) {
                convertedResult.date = convertToChinaTime(convertedResult.date);
            }

            if (convertedResult.update_date) {
                convertedResult.update_date = convertToChinaTime(convertedResult.update_date);
            }

            return convertedResult;
        });

        res.send(
            utils.returnData({
                data: convertedResults
            })
        );
    } catch (error) {
        console.error('Error fetching token usage data:', error);
        res.status(500).send('Internal Server Error');
    }
});

router.post('/submitSurveyResponse', async (req, res) => {

    const chinaTime = new Date();

    const { url, content, reasonIds, reasonDetail, pk, type } = req.body
    let newFileName = null
    if (url) {
        if (url.includes('freeapptools.com')) {
            // 提取文件名
            const fileName = path.basename(url);

            // 生成唯一标识符，例如使用日期的时间戳
            const timestamp = Date.now();
            const fileParts = fileName.split('.');
            newFileName = `${fileParts.slice(0, -1).join('.')}-${timestamp}.png`;
            console.log('Survey-url' + '====' + url + '======' + chinaTime.toLocaleString())
            console.log('Survey-newFileName' + '====' + newFileName + '======' + chinaTime.toLocaleString())
            // https://insdoss.freeapptools.com/beautyImages/1741996452109-image1741996451.058795-1741996534982-1742288776314.png
            // 设定完整目录路径
            const directoryPath = '/home/wwwroot/default/beautyImages';

            // 复制文件
            const sourceFilePath = `${directoryPath}/${fileName}`;
            const destinationFilePath = `${directoryPath}/${newFileName}`;

            try {
                fs.copyFileSync(sourceFilePath, destinationFilePath);
            } catch (error) {
                console.error(`Failed to copy file: ${error.message}`);

            }
        } else {
            newFileName = url
        }


    }

    let newurl = null
    if (!url) {
        newurl = null
    } else {
        newurl = newFileName.includes('http') ? newFileName : `https://insdoss.freeapptools.com/beautyImages/${newFileName}`
    }

    let contents = {}
    contents.id = utils.createId();
    contents.url = newurl;
    contents.content = content;
    contents.type = type;
    contents.pk = pk;
    contents.reasonIds = reasonIds;
    contents.reasonDetail = reasonDetail;
    contents.update_date = utils.formatDate(new Date());
    const { text, values } = utils.createInsertSql("beauty_questionnairedata", contents);
    let result = await utils.query(text, values);

    if (result.affectedRows == 1) {
        res.send({
            code: 0,
            msg: "success",
        });

    }
})
router.post('/storeChatMessages', async (req, res) => {
    const { uuid, content, role, eventTimeMillis } = req.body;
    let contents = {};
    contents.id = utils.createId();
    contents.uuid = uuid;
    contents.content = content;
    contents.role = role;
    contents.eventTimeMillis = eventTimeMillis;
    contents.update_date = utils.formatDate(new Date());

    try {
        // 检查uuid是否存在于beauty_user_list表中
        const checkSql = 'SELECT COUNT(*) AS count FROM beauty_user_list WHERE uuid = ?';
        const checkResult = await mysqlQuery(checkSql, [uuid]);

        if (checkResult[0].count === 0) {
            // 如果没有重复，插入新的uuid
            const insertUserSql = 'INSERT INTO beauty_user_list (id, uuid) VALUES (?, ?)';
            await mysqlQuery(insertUserSql, [utils.createId(), uuid]);
        } else {
            // 如果有重复，更新现有记录的update_date
            const updateUserSql = 'UPDATE beauty_user_list SET update_date = ? WHERE uuid = ?';
            await mysqlQuery(updateUserSql, [utils.formatDate(new Date()), uuid]);
        }

        // 插入聊天记录到beauty_aichat_list
        const insertChatSql = `
            INSERT INTO beauty_aichat_list (id, uuid, content, role, eventTimeMillis, update_date)
            VALUES (?, ?, ?, ?, ?,?)
        `;
        const insertChatResult = await mysqlQuery(insertChatSql, [utils.createId(), uuid, content, role, eventTimeMillis, utils.formatDate(new Date())]);

        if (insertChatResult.affectedRows === 1) {
            res.send({ code: 0, msg: "success" });
        } else {
            res.status(500).send({ code: 1, msg: "Failed to insert chat message" });
        }
    } catch (error) {
        console.error('Database Error:', error);
        res.status(500).send({ code: 1, msg: "Database error" });
    }
});
router.post("/getSurveyResponse", async (req, res) => {
    let { page, size } = utils.pageSize(req.body.page, req.body.size);
    let { dateRange, reason, pk, type } = req.body;
    let sqlConditions = [];

    const sqlParams = [];

    if (reason) {
        sqlConditions.push("reasonDetail LIKE ?");
        sqlParams.push(`%${reason}%`);
    }
    if (dateRange && dateRange.start && dateRange.end) {
        const startDate = moment(dateRange.start).startOf('day').format('YYYY-MM-DD HH:mm:ss');
        const endDate = moment(dateRange.end).endOf('day').format('YYYY-MM-DD HH:mm:ss');

        sqlConditions.push("update_date BETWEEN ? AND ?");
        sqlParams.push(startDate, endDate);
    }
    // 根据 pk 参数添加条件
    if (pk) {
        if (pk == "com.tcdemotc.testApp") {
            sqlConditions.push("pk = ?");
            sqlParams.push(pk);
        } else if (pk == "com.seamobitech.BeautyAI") {
            sqlConditions.push("(pk = ? OR pk IS NULL OR pk = '')");
            sqlParams.push(pk);
        }
    }
    if (type) {
        sqlConditions.push("type = ?");
        sqlParams.push(type);
    }
    let sqlBase = `SELECT * FROM beauty_questionnairedata`;
    let totalBase = "FROM beauty_questionnairedata";

    if (sqlConditions.length > 0) {
        sqlBase += ` WHERE ${sqlConditions.join(" AND ")}`;
        totalBase += ` WHERE ${sqlConditions.join(" AND ")}`;
    }

    let sqlCount = `SELECT COUNT(*) AS total_count ${totalBase}`;

    let { total } = await utils.getListSum(sqlCount, sqlParams, res, req);

    sqlBase += " ORDER BY update_date DESC LIMIT ? OFFSET ?";
    sqlParams.push(size, page);
    await pool.query(sqlBase, sqlParams, (err, result) => {
        if (err) return res.send(utils.returnData({ code: -1, err, req }));
        result.forEach((element) => {

            element.update_date = moment(element.update_date)
                .tz("Asia/Shanghai")
                .format("YYYY-MM-DD HH:mm:ss");

        });
        res.send(
            utils.returnData({
                data: result,
                total: total,
            })
        );
    });
});
router.post("/getUserList", async (req, res) => {
    let { page, size } = utils.pageSize(req.body.page, req.body.size);
    let { uuid, dateRange } = req.body;
    let sqlConditions = [];

    const sqlParams = [];
    if (uuid) {
        sqlConditions.push("uuid = ?");
        sqlParams.push(uuid);
    }
    if (dateRange && dateRange.start && dateRange.end) {
        const startDate = moment(dateRange.start).startOf('day').format('YYYY-MM-DD HH:mm:ss');
        const endDate = moment(dateRange.end).endOf('day').format('YYYY-MM-DD HH:mm:ss');

        sqlConditions.push("update_date BETWEEN ? AND ?");
        sqlParams.push(startDate, endDate);
    }


    let sqlBase = `SELECT * FROM beauty_user_list`;
    let totalBase = "FROM beauty_user_list";

    if (sqlConditions.length > 0) {
        sqlBase += ` WHERE ${sqlConditions.join(" AND ")}`;
        totalBase += ` WHERE ${sqlConditions.join(" AND ")}`;
    }

    let sqlCount = `SELECT COUNT(*) AS total_count ${totalBase}`;

    let { total } = await utils.getListSum(sqlCount, sqlParams, res, req);

    sqlBase += " ORDER BY update_date DESC LIMIT ? OFFSET ?";
    sqlParams.push(size, page);
    await pool.query(sqlBase, sqlParams, (err, result) => {
        if (err) return res.send(utils.returnData({ code: -1, err, req }));
        result.forEach((element) => {

            element.update_date = moment(element.update_date)
                .tz("Asia/Shanghai")
                .format("YYYY-MM-DD HH:mm:ss");

        });
        res.send(
            utils.returnData({
                data: result,
                total: total,
            })
        );
    });
});
router.post("/getChatList", async (req, res) => {
    const { uuid } = req.body;

    const sqlParams = [];
    let sqlConditions = []; // 初始化SQL条件数组

    if (uuid) {
        sqlConditions.push("uuid = ?");
        sqlParams.push(uuid);
    }

    let sqlBase = `SELECT * FROM beauty_aichat_list`;
    let totalBase = "FROM beauty_aichat_list";

    // 如果有额外的条件，添加到基础SQL中
    if (sqlConditions.length > 0) {
        const whereClause = sqlConditions.join(' AND ');
        sqlBase += ` WHERE ${whereClause}`;
        totalBase += ` WHERE ${whereClause}`;
    }

    let sqlCount = `SELECT COUNT(*) AS total_count ${totalBase}`;

    //获取总数
    let { total } = await utils.getListSum(sqlCount, sqlParams, res, req);

    // 排序但不分页
    sqlBase += " ORDER BY eventTimeMillis ASC";

    // 执行查询
    await pool.query(sqlBase, sqlParams, (err, result) => {
        if (err) return res.send(utils.returnData({ code: -1, err, req }));
        result.forEach((element) => {
            element.update_date = moment(element.update_date)
                .tz("Asia/Shanghai")
                .format("YYYY-MM-DD HH:mm:ss");
        });
        res.send(
            utils.returnData({
                data: result,
                total: total,
            })
        );
    });
});


async function deleteFile(directory, filename) {
    // 构建要删除文件的完整路径
    const filePath = path.join(directory, filename);

    fs.unlink(filePath, (err) => {
        if (err) {
            if (err.code === 'ENOENT') {
                console.error('File not found:', filePath);
            } else {
                console.error('Error deleting file:', err);
            }
            return;
        }
        console.log('File deleted successfully:', filePath);
    });
}
//扫描条形码上报失败信息接口
router.post('/reportBarcodeScanFailure', async (req, res) => {
    console.log(req.body, '扫码上报')
    const { pk, image, qrcode, code, reason, version } = req.body;

    try {
        const id = utils.createId();
        const currentDate = utils.formatDate(new Date());

        const sql = `
            INSERT INTO beauty_product_error
                (id, pk,version, image, qrcode, code, reason, create_date, update_date)
            VALUES 
                (?, ?,?, ?, ?, ?, ?, ?, ?)
        `;
        const arr = [id, pk, version, image, qrcode, code, reason, currentDate, currentDate];

        const result = await mysqlQuery(sql, arr);

        res.send({
            code: 0,
            msg: 'success'
        })

    } catch (error) {
        res.send({
            code: 1,
            msg: error.message
        })
    }
});
router.post('/getqrErrorList', async (req, res) => {
    let { page, size } = utils.pageSize(req.body.page, req.body.size);
    let { type, dateRange, pk } = req.body;
    let sqlConditions = [];
    const sqlParams = [];
    if (type) {
        sqlConditions.push("type = ?");
        sqlParams.push(type);
    }
    if (dateRange && dateRange.start && dateRange.end) {
        const startDate = moment(dateRange.start).startOf('day').format('YYYY-MM-DD HH:mm:ss');
        const endDate = moment(dateRange.end).endOf('day').format('YYYY-MM-DD HH:mm:ss');

        sqlConditions.push("update_date BETWEEN ? AND ?");
        sqlParams.push(startDate, endDate);
    }
    // 根据 pk 参数添加条件
    if (pk) {
        if (pk == "com.tcdemotc.testApp") {
            sqlConditions.push("pk = ?");
            sqlParams.push(pk);
        } else if (pk == "com.seamobitech.BeautyAI") {
            sqlConditions.push("(pk = ? OR pk IS NULL OR pk = '')");
            sqlParams.push(pk);
        }
    }
    sqlConditions.push("state = 0");
    // 添加条件：确保 message 字段有值

    let sqlBase = `SELECT * FROM beauty_product_error `;
    let totalBase = "FROM beauty_product_error";

    if (sqlConditions.length > 0) {
        sqlBase += ` WHERE ${sqlConditions.join(" AND ")}`;
        totalBase += ` WHERE ${sqlConditions.join(" AND ")}`;
    }

    let sqlCount = `SELECT COUNT(*) AS total_count ${totalBase}`;

    let { total } = await utils.getListSum(sqlCount, sqlParams, res, req);

    sqlBase += " ORDER BY update_date DESC LIMIT ? OFFSET ?";
    sqlParams.push(size, page);
    await pool.query(sqlBase, sqlParams, (err, result) => {
        if (err) return res.send(utils.returnData({ code: -1, err, req }));
        result.forEach((element) => {

            element.update_date = moment(element.update_date)
                .tz("Asia/Shanghai")
                .format("YYYY-MM-DD HH:mm:ss");

        });
        res.send(
            utils.returnData({
                data: result,
                total: total,
            })
        );
    });
})
//删除单条条形码上报
router.post("/deleteHandelQrcodeError", async (req, res) => {
    let { id, image } = req.body;
    if (image) {
        // 使用示例
        const directoryPath = '/home/wwwroot/default/beautyImages';
        const fileName = getFileName(image);

        deleteFile(directoryPath, fileName);
    }

    const sql = `UPDATE beauty_product_error SET state = 1 WHERE id = ?`;
    let result = await mysqlQuery(sql, [id])

    res.send(
        {
            code: 0,
            msg: 'success'
        }
    );
});
//批量删除条形码上报
router.post("/batchDeleteHandelQrcodeError", async (req, res) => {
    const items = req.body.items;

    const directoryPath = '/home/wwwroot/default/beautyImages';

    for (const item of items) {
        const { id, image } = item;
        if (image) {
            const fileName = getFileName(image);

            await deleteFile(directoryPath, fileName);
        }


        const sql = `UPDATE beauty_product_error SET state = 1 WHERE id = ?`;
        await mysqlQuery(sql, [id]);
    }

    res.send({
        code: 0,
        msg: 'success'
    });
});
//拍照查产品上报竞品信息接口
router.post('/reportCompetitorProductInfo', async (req, res) => {
    const { pk, version, infoList, image } = req.body;

    try {
        // 如果 infoList 是数组或对象，则转换为 JSON 字符串后存储
        // const infoListString = JSON.stringify(infoList);

        // 根据要求生成 id 和时间数据
        const id = utils.createId();
        const currentDate = utils.formatDate(new Date());

        // SQL 语句，注意字段名称需要与数据库中的一致
        const sql = `
            INSERT INTO beauty_product_info
                (id, pk,version,image, infoList, create_date, update_date)
            VALUES 
                (?, ?,?, ?, ?, ?,?)
        `;
        const arr = [id, pk, version, image, infoList, currentDate, currentDate];

        // 执行数据库插入操作
        const result = await mysqlQuery(sql, arr);

        // 返回成功信息
        res.send({
            code: 0,
            msg: 'success'
        })
    } catch (error) {
        res.send({
            code: 1,
            msg: error.message
        })
    }
});
router.post('/getProductInfoList', async (req, res) => {
    let { page, size } = utils.pageSize(req.body.page, req.body.size);
    let { type, dateRange, pk } = req.body;
    let sqlConditions = [];
    const sqlParams = [];
    if (type) {
        sqlConditions.push("type = ?");
        sqlParams.push(type);
    }
    if (dateRange && dateRange.start && dateRange.end) {
        const startDate = moment(dateRange.start).startOf('day').format('YYYY-MM-DD HH:mm:ss');
        const endDate = moment(dateRange.end).endOf('day').format('YYYY-MM-DD HH:mm:ss');

        sqlConditions.push("update_date BETWEEN ? AND ?");
        sqlParams.push(startDate, endDate);
    }
    // 根据 pk 参数添加条件
    if (pk) {
        if (pk == "com.tcdemotc.testApp") {
            sqlConditions.push("pk = ?");
            sqlParams.push(pk);
        } else if (pk == "com.seamobitech.BeautyAI") {
            sqlConditions.push("(pk = ? OR pk IS NULL OR pk = '')");
            sqlParams.push(pk);
        }
    }
    sqlConditions.push("state = 0");
    // 添加条件：确保 message 字段有值

    let sqlBase = `SELECT * FROM beauty_product_info `;
    let totalBase = "FROM beauty_product_info";

    if (sqlConditions.length > 0) {
        sqlBase += ` WHERE ${sqlConditions.join(" AND ")}`;
        totalBase += ` WHERE ${sqlConditions.join(" AND ")}`;
    }

    let sqlCount = `SELECT COUNT(*) AS total_count ${totalBase}`;

    let { total } = await utils.getListSum(sqlCount, sqlParams, res, req);

    sqlBase += " ORDER BY update_date DESC LIMIT ? OFFSET ?";
    sqlParams.push(size, page);
    await pool.query(sqlBase, sqlParams, (err, result) => {
        if (err) return res.send(utils.returnData({ code: -1, err, req }));
        result.forEach((element) => {

            element.update_date = moment(element.update_date)
                .tz("Asia/Shanghai")
                .format("YYYY-MM-DD HH:mm:ss");

        });
        res.send(
            utils.returnData({
                data: result,
                total: total,
            })
        );
    });
})
//删除 竞品商品信息
router.post("/deleteHandelProductInfo", async (req, res) => {
    let { id, image } = req.body;
    if (image) {
        // 使用示例
        const directoryPath = '/home/wwwroot/default/beautyImages';
        const fileName = getFileName(image);

        deleteFile(directoryPath, fileName);
    }

    const sql = `UPDATE beauty_product_info SET state = 1 WHERE id = ?`;
    let result = await mysqlQuery(sql, [id])

    res.send(
        {
            code: 0,
            msg: 'success'
        }
    );
});
//批量竞品商品信息
router.post("/batchDeleteHandelProductInfo", async (req, res) => {
    const items = req.body.items;

    const directoryPath = '/home/wwwroot/default/beautyImages';

    for (const item of items) {
        const { id, image } = item;
        if (image) {
            const fileName = getFileName(image);

            await deleteFile(directoryPath, fileName);
        }


        const sql = `UPDATE beauty_product_info SET state = 1 WHERE id = ?`;
        await mysqlQuery(sql, [id]);
    }

    res.send({
        code: 0,
        msg: 'success'
    });
});



//imageToProduct
router.post("/getimageToProductConfig", async (req, res) => {
    const { version } = req.body
    let sql = `SELECT * FROM imageToProduct_recognition WHERE version = ?`;
    let result = await mysqlQuery(sql, [version]);
    res.send({ code: 0, data: result[0] })
});
router.post("/updateimageToProductConfig", async (req, res) => {
    let { name } = await utils.getUserInfo(req, res);
    const { prompt_test, temperature_test, max_tokens_test, top_p_test, frequency_penalty_test, presence_penalty_test, model_test, id } = req.body
    const sql = `
            UPDATE imageToProduct_recognition
            SET 
                prompt_test = ?, 
                temperature_test = ?, 
                max_tokens_test = ?, 
                top_p_test = ?, 
                frequency_penalty_test = ?, 
                presence_penalty_test = ?, 
                model_test = ?, 
                update_by = ?,
                update_date = ?
            WHERE id = ?
        `;
    let result = await mysqlQuery(sql, [prompt_test, temperature_test, max_tokens_test, top_p_test, frequency_penalty_test, presence_penalty_test, model_test, name, utils.formatDate(new Date()), id])
    res.send({ code: 0, data: result[0] })
});
router.post("/updateimageToProductConfigHandel", async (req, res) => {
    let { name } = await utils.getUserInfo(req, res);
    const { prompt_test, temperature_test, max_tokens_test, top_p_test, frequency_penalty_test, presence_penalty_test, model_test, id } = req.body
    const sql = `
            UPDATE imageToProduct_recognition
            SET 
                prompt_release = ?,
                temperature = ?,
                max_tokens = ?,
                top_p = ?,
                frequency_penalty = ?,
                presence_penalty = ?,
                model = ?,
                update_by = ?,
                update_date = ?
            WHERE id = ?
        `;
    let result = await mysqlQuery(sql, [prompt_test, temperature_test, max_tokens_test, top_p_test, frequency_penalty_test, presence_penalty_test, model_test, name, utils.formatDate(new Date()), id])
    res.send({ code: 0, data: result[0] })
});
router.post('/getimageToProductVersionList', async (req, res) => {
    try {
        let sql = `SELECT version FROM imageToProduct_recognition`;
        let result = await mysqlQuery(sql, []);
        const formattedResult = result.map(row => ({
            id: row.version,
            name: row.version
        }));

        res.send({
            data: formattedResult
        });
    } catch (error) {
        console.error("Error fetching versions:", error);
        res.status(500).json({ error: "Internal server error" });
    }
});

router.post('/addimageToProductVersion', async (req, res) => {
    const { version } = req.body;
    try {
        // 检查表中是否已经有相同的版本
        const existingVersion = await mysqlQuery('SELECT 1 FROM imageToProduct_recognition WHERE version = ?', [version]);
        if (existingVersion.length > 0) {
            return res.send({ code: 1, msg: '版本已存在' });
        }
        // 获取表中最后一条记录
        const lastRecord = await mysqlQuery('SELECT * FROM imageToProduct_recognition ORDER BY id DESC LIMIT 1', []);

        if (lastRecord.length === 0) {
            return res.send({ code: 1, msg: '没有可复制的记录' });
        }
        // 从最后一条记录中获取值，并修改版本
        let newRecord = { ...lastRecord[0], version };

        delete newRecord.id;
        newRecord.id = utils.createId();
        // 插入新记录
        const insertQuery = 'INSERT INTO imageToProduct_recognition SET ?';
        await mysqlQuery(insertQuery, [newRecord]);

        res.send({ code: 0, msg: 'success' })

    } catch (error) {
        console.error(error);
        res.status(500).json({ error: '数据库操作失败，请稍后重试' });
    }
})
router.post("/imageToProduct-analysis", async (req, res) => {

    let parseData = req.body.query;
    let content = JSON.parse(decrypt(parseData, config.cryptoKey, config.cryptoIv));
    const chinaTime = new Date();
    console.log(content, 'content' + chinaTime.toLocaleString())
    if (content.code != 'OZqcJQLBUyevTh') return
    const { imageUrl, version, pk } = content


    let sql = `SELECT * FROM imageToProduct_recognition WHERE version = ?`;
    let params = [version || '1.0.12']; // Use the specified version or the default version
    let result = await mysqlQuery(sql, params);
    // If there is no result for the specified version, query all versions and get the latest one
    if (result.length === 0 && version) {
        sql = `
         SELECT * FROM imageToProduct_recognition
        ORDER BY id DESC
        LIMIT 1
    `;
        result = await mysqlQuery(sql);
    }






    const { prompt_release, temperature, max_tokens, top_p, frequency_penalty, presence_penalty, model } = result[0]
    try {
        // 将 imageUrl 转换为 Base64
        // const imageResponse = await axios.get(imageUrl, { responseType: 'arraybuffer' });
        // const imageBase64 = Buffer.from(imageResponse.data, 'binary').toString('base64');
        // console.log(imageBase64)
        const response = await axios.post(
            'https://api.openai.com/v1/chat/completions',
            {
                model: model,
                messages: [
                    {
                        role: 'system',
                        content: [
                            {
                                type: 'text',
                                text: prompt_release
                            }
                        ]
                    },
                    {
                        role: 'user',
                        content: [
                            {
                                type: 'image_url',
                                image_url: {
                                    // url: 'https://www.the-sun.com/wp-content/uploads/sites/6/2020/05/stassi.jpeg'
                                    url: imageUrl
                                }
                            }
                        ]
                    }
                ],
                response_format: {
                    type: 'text'
                },
                temperature: Number(temperature),
                max_tokens: Number(max_tokens),
                top_p: Number(top_p),
                frequency_penalty: Number(frequency_penalty),
                presence_penalty: Number(presence_penalty)
            },
            {
                headers: {
                    'Content-Type': 'application/json',
                    'Authorization': `Bearer   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`
                }
            }
        );
        console.log(imageUrl, '结果' + chinaTime.toLocaleString())

        // 获取 token 消耗信息
        const { usage } = response.data;
        const { prompt_tokens, completion_tokens, total_tokens } = usage;
        // 存储 token 消耗到数据库
        const date = utils.formatDate1(new Date());
        let id = utils.createId();
        // 需要插入的内容
        let message = null;
        let url = imageUrl

        // 如果 completion_tokens 小于 200，则存储 message
        // if (completion_tokens < 200) {
        //     message = JSON.stringify(response.data.choices[0].message);

        // }
        if (response.data.choices[0].message) {
            message = JSON.stringify(response.data.choices[0].message);

        }
        const messageData = JSON.parse(message);
        const parsedData = extractAndParseJson(messageData.content);
        let code = parsedData.error_info.error_code
        const insertSql = `INSERT INTO ai_tokens (id,pk,code,version,date,type, prompt_tokens, completion_tokens, total_tokens,message,url,model,create_date,update_date) VALUES (?,?,?,?, ?,?,?, ?,?,?,?, ?, ?,?)`;
        await mysqlQuery(insertSql, [id, pk, code, version, date, 'imageToProduct', prompt_tokens, completion_tokens, total_tokens, message, url, model, utils.formatDate(new Date()), utils.formatDate(new Date())]);
        let data = encrypt(
            JSON.stringify(response.data.choices[0].message),
            config.cryptoKey,
            config.cryptoIv
        );
        res.send({
            code: 0,
            msg: 'success',
            data: data
        })
    } catch (error) {
        console.error('Error making API request:', error.response ? error.response.data : error.message);
        res.send({
            code: 1,
            msg: error.response ? error.response.data : error.message,
        })
    }

});
async function mysqlQuery(sql, arr) {

    return new Promise((resolve, reject) => {
        pool.query(sql, arr, (error, results, fields) => {

            if (error) {
                console.log(error)
                return reject(error);
            }
            resolve(results);
        });
    });
}

//分享裂变

/**
 * 生成6位由大写字母和数字组成的不重复邀请码
 * @returns {string} 6位邀请码
 */
async function generateInviteCode() {
    const characters = 'ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789';
    let inviteCode;
    let isUnique = false;

    while (!isUnique) {
        inviteCode = '';
        for (let i = 0; i < 6; i++) {
            const randomIndex = Math.floor(Math.random() * characters.length);
            inviteCode += characters[randomIndex];
        }

        // 检查邀请码是否已存在
        const checkSql = 'SELECT COUNT(*) AS count FROM beauty_users WHERE invite_code = ?';
        const result = await mysqlQuery(checkSql, [inviteCode]);
        if (result[0].count === 0) {
            isUnique = true;
        }
    }

    return inviteCode;
}

/**
 * 用户注册接口
 */
router.post('/registerUser', async (req, res) => {
    const { uuid } = req.body;

    if (!uuid) {
        return res.send({ code: 1, msg: 'UUID is required' })
    }

    try {
        // 检查 uuid 是否已存在
        const checkSql = 'SELECT * FROM beauty_users WHERE uuid = ?';
        const existingUsers = await mysqlQuery(checkSql, [uuid]);

        if (existingUsers.length > 0) {
            // 如果存在，返回现有用户数据
            return res.send({
                code: 0,
                data: existingUsers[0],
                msg: 'success'
            })
        } else {
            // 如果不存在，创建新用户
            const id = utils.createId(); // 生成新的用户ID
            const inviteCode = await generateInviteCode(); // 生成唯一的邀请码
            const insertSql = `
                INSERT INTO beauty_users (id, uuid, invite_code, total_invite, total_free_count, used_free_count, account_status, created_date, update_date)
                VALUES (?, ?, ?, 0, 0, 0, 0, NOW(), NOW())
            `;
            await mysqlQuery(insertSql, [id, uuid, inviteCode]);

            // 返回新创建的用户数据
            const newUser = {
                id,
                uuid,
                invite_code: inviteCode,
                total_invite: 0,
                total_free_count: 0,
                used_free_count: 0,
                remaining_free_count: 0,
                account_status: 0,
                created_date: new Date(),
                update_date: new Date()
            };
            return res.send({
                code: 0,
                data: newUser,
                msg: 'success'
            })
        }
    } catch (error) {
        console.error('Error in registerUser:', error);
        return res.send({
            code: 1,
            msg: 'Internal server error'
        });

    }
});

/**
 * 通过 UUID 获取用户个人数据的接口
 */
router.post('/getUserByUuid', async (req, res) => {
    const { uuid } = req.body;

    if (!uuid) {
        return res.send({ code: 1, msg: 'UUID is required' })
    }

    try {
        // 查询数据库，获取对应的用户数据
        const sql = 'SELECT * FROM beauty_users WHERE uuid = ?';
        const users = await mysqlQuery(sql, [uuid]);

        if (users.length > 0) {
            // 如果找到用户，返回用户数据
            return res.send({
                code: 0,
                data: users[0],
                msg: 'success'
            })
        } else {
            // 如果未找到用户 
            return res.send({
                code: 1,
                msg: 'User not found'
            })
        }
    } catch (error) {
        console.error('Error in getUserByUuid:', error);
        return res.send({
            code: 500,
            msg: 'Internal server error'
        })
    }
});

/**
 * 绑定邀请关系的接口
 */
router.post('/bindInvite', async (req, res) => {
    const { uuid, invite_code } = req.body;

    if (!uuid || !invite_code) {
        return res.send({
            code: 1,
            msg: 'UUID and invite code are required'
        })
    }

    try {
        // 获取被邀请者的信息
        const inviteeSql = 'SELECT id,uuid FROM beauty_users WHERE uuid = ?';
        const inviteeResult = await mysqlQuery(inviteeSql, [uuid]);

        if (inviteeResult.length === 0) {
            return res.send({
                code: 2,
                msg: 'No invitees found'
            })
        }

        const inviteeId = inviteeResult[0].uuid;

        // 获取邀请者的信息
        const inviterSql = 'SELECT id,uuid, total_invite, total_free_count, used_free_count FROM beauty_users WHERE invite_code = ?';
        const inviterResult = await mysqlQuery(inviterSql, [invite_code]);

        if (inviterResult.length === 0) {
            return res.send({
                code: 3,
                msg: 'Invalid invite code'
            })
        }
        const inviterId = inviterResult[0].uuid;
        if (inviterId == inviteeId) {
            return res.send({
                code: 6,
                msg: 'Cannot invite yourself'
            });
        }

        // 检查是否已存在邀请关系
        const checkInviteSql = 'SELECT id FROM beauty_user_invites WHERE inviter_id = ? AND invitee_id = ?';
        const existingInvite = await mysqlQuery(checkInviteSql, [inviterId, inviteeId]);

        if (existingInvite.length > 0) {
            return res.send({
                code: 4,
                msg: 'Invite relationship already exists'
            })
        }

        // 获取 SKInviteCount 配置值
        const configSql = 'SELECT SKInviteCount FROM beauty_invite_config LIMIT 1';
        const configResult = await mysqlQuery(configSql);

        if (configResult.length === 0) {
            return res.send({
                code: 5,
                msg: 'Invite configuration not found'
            })
        }

        const SKInviteCount = configResult[0].SKInviteCount;

        // 插入新的邀请关系
        const inviteId = utils.createId(); // 生成新的邀请关系ID
        const insertInviteSql = `
                INSERT INTO beauty_user_invites (id, inviter_id, invitee_id, invite_code, created_date)
                VALUES (?, ?, ?, ?, NOW())
            `;
        await mysqlQuery(insertInviteSql, [inviteId, inviterId, inviteeId, invite_code]);

        // 计算新的 total_invite
        const newTotalInvite = inviterResult[0].total_invite + 1;

        // 计算新的 total_free_count
        const newTotalFreeCount = Math.floor(newTotalInvite / SKInviteCount);

        // 计算新的 remaining_free_count
        const newRemainingFreeCount = (newTotalFreeCount - inviterResult[0].used_free_count) <= 0
            ? 0
            : newTotalFreeCount - inviterResult[0].used_free_count;

        // 更新邀请者的统计信息
        const updateInviterSql = `
                UPDATE beauty_users
                SET total_invite = ?,
                    total_free_count = ?,
                    remaining_free_count = ?,
                    update_date = NOW()
                WHERE uuid = ?
            `;
        await mysqlQuery(updateInviterSql, [newTotalInvite, newTotalFreeCount, newRemainingFreeCount, inviterId]);

        // 获取更新后邀请者的信息
        const updatedInviterResult = await mysqlQuery(inviterSql, [invite_code]);
        const updatedInviterData = updatedInviterResult[0];

        return res.send({
            code: 0,
            msg: 'success',
            data: updatedInviterData // 返回更新后的邀请者数据
        });

    } catch (error) {
        console.error('Error in bindInvite:', error);
        return res.send({
            code: 500,
            msg: 'Internal server error'
        })
    }
});
/**
 * 更新用户免费次数的接口
 */
router.post('/useFreeCount', async (req, res) => {
    const { uuid } = req.body;

    if (!uuid) {
        return res.send({
            code: 3,
            msg: 'UUID is required'
        });
    }

    try {
        // 获取用户的当前信息
        const userSql = 'SELECT used_free_count, remaining_free_count FROM beauty_users WHERE uuid = ?';
        const userResult = await mysqlQuery(userSql, [uuid]);

        if (userResult.length === 0) {
            return res.send({
                code: 2,
                msg: 'User not found'
            });
        }

        let { used_free_count, remaining_free_count } = userResult[0];

        // 检查 remaining_free_count 是否为 0
        if (remaining_free_count <= 0) {
            return res.send({
                code: 1,
                msg: 'No remaining free counts available'
            });
        }

        // 更新 used_free_count 和 remaining_free_count
        used_free_count += 1;
        remaining_free_count -= 1;

        // 更新数据库
        const updateSql = `
            UPDATE beauty_users
            SET used_free_count = ?,
                remaining_free_count = ?,
                update_date = NOW()
            WHERE uuid = ?
        `;
        await mysqlQuery(updateSql, [used_free_count, remaining_free_count, uuid]);

        // 再次查询用户的最新信息
        const updatedUserResult = await mysqlQuery(userSql, [uuid]);
        const updatedUserData = updatedUserResult[0];

        return res.send({
            code: 0,
            msg: 'success',
            data: updatedUserData  // 将更新后的用户数据返回
        });
    } catch (error) {
        console.error('Error in useFreeCount:', error);
        return res.send({
            code: 500,
            msg: 'Internal server error'
        });
    }
});

/**
 * 更新 beauty_invite_config 表的接口
 */
router.post('/updateInviteConfig', async (req, res) => {
    const { SKInviteCount, SKInviteShareBtnLink } = req.body;

    // 参数校验
    if (SKInviteCount === undefined || SKInviteShareBtnLink === undefined) {
        return res.status(400).json({ error: 'SKInviteCount and SKInviteShareBtnLink are required' });
    }

    // 验证SKInviteCount类型和范围
    const inviteCount = Number(SKInviteCount);
    if (inviteCount <= 0) {
        return res.status(400).json({ error: 'SKInviteCount must be greater than 0' });
    }

    try {
        // 获取旧配置
        const [currentConfig] = await mysqlQuery('SELECT SKInviteCount FROM beauty_invite_config WHERE id = 1');
        const oldCount = Number(currentConfig.SKInviteCount);

        // 更新配置表
        const updateConfigSql = `
            UPDATE beauty_invite_config
            SET SKInviteCount = ?,
                SKInviteShareBtnLink = ?
            WHERE id = 1
        `;
        const result = await mysqlQuery(updateConfigSql, [inviteCount, SKInviteShareBtnLink]);

        if (result.affectedRows === 0) {
            return res.status(404).json({ error: 'Configuration not found' });
        }

        // 如果SKInviteCount发生变化才更新用户表
        if (inviteCount !== oldCount) {
            const updateUsersSql = `
                UPDATE beauty_users
                SET 
                    total_free_count = GREATEST(FLOOR(total_invite / ?), 0),
                    remaining_free_count = GREATEST(
                        GREATEST(FLOOR(total_invite / ?), 0) - used_free_count, 
                        0
                    )
            `;
            await mysqlQuery(updateUsersSql, [inviteCount, inviteCount]);
        }

        return res.status(200).json({ message: 'Configuration updated successfully' });
    } catch (error) {
        console.error('Error in updateInviteConfig:', error);
        return res.status(500).json({ error: 'Internal server error' });
    }
});

//获取beauty_invite_config 表的接口
router.post('/getInviteConfig', async (req, res) => {
    try {
        const updateSql = `select * from beauty_invite_config`;
        const result = await mysqlQuery(updateSql, []);
        res.send({
            code: 0,
            data: result[0]
        })
    } catch (error) {
        console.error('Error in updateInviteConfig:', error);
        return res.status(500).json({ error: 'Internal server error' });
    }
});

//获取用户列表
router.post('/getBeautyUserList', async (req, res) => {
    let { page, size } = utils.pageSize(req.body.page, req.body.size);
    let { uuid, dateRange, account_status, orderBy } = req.body;
    let sqlConditions = [];
    const sqlParams = [];
    if (uuid) {
        sqlConditions.push("uuid = ?");
        sqlParams.push(uuid);
    }
    if (dateRange && dateRange.start && dateRange.end) {
        const startDate = moment(dateRange.start).startOf('day').format('YYYY-MM-DD HH:mm:ss');
        const endDate = moment(dateRange.end).endOf('day').format('YYYY-MM-DD HH:mm:ss');

        sqlConditions.push("update_date BETWEEN ? AND ?");
        sqlParams.push(startDate, endDate);
    }
     
    sqlConditions.push("account_status = 0");
    // 添加条件：确保 message 字段有值

    let sqlBase = `SELECT * FROM beauty_users`;
    let totalBase = "FROM beauty_users";

    if (sqlConditions.length > 0) {
        sqlBase += ` WHERE ${sqlConditions.join(" AND ")}`;
        totalBase += ` WHERE ${sqlConditions.join(" AND ")}`;
    }

    let sqlCount = `SELECT COUNT(*) AS total_count ${totalBase}`;

    let { total } = await utils.getListSum(sqlCount, sqlParams, res, req);

    // 排序字段白名单，避免SQL注入
    let orderField = "update_date";
    let orderDirection = "DESC";
    if (orderBy === "total_invite") {
        orderField = "total_invite";
    }
    // 如需额外排序方式，可在这里赋值

    sqlBase += ` ORDER BY ${orderField} ${orderDirection} LIMIT ? OFFSET ?`;
    sqlParams.push(size, page);
    await pool.query(sqlBase, sqlParams, (err, result) => {
        if (err) return res.send(utils.returnData({ code: -1, err, req }));
        result.forEach((element) => {

            element.created_date = moment(element.created_date)
                .tz("Asia/Shanghai")
                .format("YYYY-MM-DD HH:mm:ss");
            element.update_date = moment(element.update_date)
                .tz("Asia/Shanghai")
                .format("YYYY-MM-DD HH:mm:ss");
        });
        res.send(
            utils.returnData({
                data: result,
                total: total,
            })
        );
    });
})
//获取用户邀请
router.post('/getBeautyInviteList', async (req, res) => {
    // Parse pagination parameters
    let { page, size } = utils.pageSize(req.body.page, req.body.size);
    const { uuid } = req.body;

    try {
        // SQL query to get the total count of invitees for the given uuid
        const getTotalCountSQL = `
            SELECT COUNT(*) AS total
            FROM beauty_user_invites
            WHERE inviter_id = ?;
        `;

        // Execute query to get the total count of invitees
        const totalCountResult = await mysqlQuery(getTotalCountSQL, [uuid]);
        const total = totalCountResult[0].total;

        if (total === 0) {
            return res.json({ data: [], total: 0, message: "No invitees found." });
        }

        // SQL query to get invitee IDs for the given inviter ID (uuid) from beauty_user_invites table
        const getInviteesSQL = `
            SELECT invitee_id
            FROM beauty_user_invites
            WHERE inviter_id = ?
            LIMIT ?, ?;
        `;

        // Execute query to get invitee IDs, with pagination
        const inviteeIds = await mysqlQuery(getInviteesSQL, [uuid, page, size]);

        // Extract invitee IDs and prepare them for usage in subsequent query
        const inviteeUuidList = inviteeIds.map(row => row.invitee_id);

        // SQL query to get user details from beauty_users table
        const getUserDetailsSQL = `
            SELECT *
            FROM beauty_users
            WHERE uuid IN (?)
        `;

        // Execute the query to get user details
        const userDetails = await mysqlQuery(getUserDetailsSQL, [inviteeUuidList]);
        userDetails.forEach((element) => {

            element.created_date = moment(element.created_date)
                .tz("Asia/Shanghai")
                .format("YYYY-MM-DD HH:mm:ss");
        });
        // Send the user details and total as the response
        res.json({ data: userDetails, total });

    } catch (error) {
        console.error("Error fetching invitee list: ", error);
        res.status(500).json({ error: "An error occurred while fetching the invitee list." });
    }
});
module.exports = router;
